package com.wip.reportingservice.daos.impls;

import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.SQLQuery;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.Disjunction;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.Transformers;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.wip.reportingservice.daos.entities.PageClientTimeTable;
import com.wip.reportingservice.daos.entities.ReportDataTable;
import com.wip.reportingservice.daos.interfaces.IPageClientTimeDao;
import com.wip.reportingservice.daos.interfaces.IReportDataDao;

@Repository
@Transactional
public class ReportDataDaoImpl implements IReportDataDao {

	private static final Logger logger = LoggerFactory
			.getLogger(ReportDataDaoImpl.class);

	@Autowired
	private SessionFactory sessionFactory;

	public List<ReportDataTable> getRecords(List<String> pages,
			List<String> clients, List<String> logs) {

		StringBuilder sQuery = new StringBuilder();
		sQuery.append("");
		sQuery.append("SELECT a.id as id,s.page_name as pageName,d.client_name as clientName,b.log_file_date as logName, a.access_time as accessTime \r");
		sQuery.append("FROM PageClientTimeTable a  \r");
		sQuery.append("INNER JOIN PagesTable s ON a.page_id = s.page_id  \r");
		sQuery.append("INNER JOIN ClientsTable      d ON a.client_id   = d.client_id  \r");
		sQuery.append("INNER JOIN LogsTable       b ON a.log_id   = b.log_id \r");
		sQuery.append("WHERE s.page_name in ");
		
		sQuery.append("( ");
		
		StringBuilder sbpages = new StringBuilder();
		for (String pg:pages)
		{
			sbpages.append("'"+pg+"' ,");
		}
		
		sQuery.append(sbpages.substring(0, sbpages.length()-1));
		sQuery.append(" ) \r");
		sQuery.append("AND d.client_name in ");
		sQuery.append("( ");	
		StringBuilder sbclients = new StringBuilder();
		for (String cl:clients)
		{
			sbclients.append("'"+cl+"' ,");
		}
		sQuery.append(sbclients.substring(0, sbclients.length()-1));
		sQuery.append(" ) \r");
		
		sQuery.append("AND b.log_file_date in ");
		
		sQuery.append("( ");	
		StringBuilder sblogs = new StringBuilder();
		for (String lg:logs)
		{
			sblogs.append("'"+lg+"' ,");
		}
		sQuery.append(sblogs.substring(0, sblogs.length()-1));
		sQuery.append(" ) \r");
		
		System.out.println(sQuery);
		SQLQuery query = sessionFactory.getCurrentSession().createSQLQuery(sQuery.toString());

		query.setResultTransformer(Transformers
				.aliasToBean(ReportDataTable.class));

		return (List<ReportDataTable>) query.list();

	}

}
